package com.verbetia.musictrends.persistence.hsqldb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class MusicsPersMngr {

	Connection conn;
	private static String DBNAME = "musictrends";
//	private static String CONNECTION_STRING="jdbc:hsqldb:C:\\eclipse32\\brainzidx\\BrainzSearch\\WEB-INF\\lib\\musictrends.db";
	private static String CONNECTION_STRING="jdbc:hsqldb:hsql://localhost:9001";

	
	
	public MusicsPersMngr()	
	{
		try {
			Class.forName("org.hsqldb.jdbcDriver");
		} catch (ClassNotFoundException cnfe)
		{
			System.out.println("Error loading org.hsqldb.jdbcDriver " + cnfe);
		}
   
		try {
					
        conn = DriverManager.getConnection(MusicsPersMngr.CONNECTION_STRING,        		
                                           "sa",                     // username
                                           "");                      // password
        
		}
		catch (SQLException sqle)
		{
			System.out.println(sqle);
			
		}
    }
				

	
	
	public synchronized void createInstance(String userid, String musicdata, String title, String artist, String album, String amazon_id)
		throws SQLException
	{
		
		String query = "INSERT INTO music (id, date, userid, musicdata, title, artist, album, amazon_id) VALUES( NULL, NOW(), "+ 
						userid +", '"+ 
						musicdata +"','" +
						title +"','" +
						artist +"','" +
						album +"','" +
						amazon_id +"')";
						
		
		Statement st = null;

	     st = conn.createStatement();    // statements

	        
	        
	    int i = st.executeUpdate(query);    // run the query

	    if (i == -1) {
	            System.out.println("db error : " + query);
	    }

	    st.close();
				
	}
	
	
	public synchronized ResultSet selectQuery(String userid, int limit)
		throws SQLException
	{
	
	ResultSet rs = null;
	String query = "SELECT id, date, musicdata,  title, artist, album, amazon_id  FROM music WHERE userid="+ userid + " ORDER BY date DESC"; 
	
	Statement st = null;

    st = conn.createStatement();    // statements
               
    rs  = st.executeQuery(query);    // run the query
    
   // dump(rs);

    st.close();

    return rs;
    
	}
	
	public synchronized void removeInstance(String id) 
		throws SQLException
	{
		//DELETE FROM "table_name" WHERE {condition}
		String query = "DELETE FROM music WHERE id="+ id; 
		
		Statement st = null;
		
		st = conn.createStatement();    // statements
						
		int i = st.executeUpdate(query);    // run the query
		
		if (i == -1) {
		System.out.println("db error : " + query);
		}
		
		st.close();
		
		
	}
	
	
    public static void dump(ResultSet rs) throws SQLException {

        // the order of the rows in a cursor
        // are implementation dependent unless you use the SQL ORDER statement
        ResultSetMetaData meta   = rs.getMetaData();
        int               colmax = meta.getColumnCount();
        int               i;
        Object            o = null;

        // the result set is a cursor into the data.  You can only
        // point to one row at a time
        // assume we are pointing to BEFORE the first row
        // rs.next() points to next row and returns true
        // or false if there is no next row, which breaks the loop
        for (; rs.next(); ) {
            for (i = 0; i < colmax; ++i) {
                o = rs.getObject(i + 1);    // Is SQL the first column is indexed

                // with 1 not 0
                System.out.print(o.toString() + " ");
            }

            System.out.println(" ");
        }
    }                                       //void dump( ResultSet rs )

	
	
	 public static void main(String[] args) {
		 
		 MusicsPersMngr m = new MusicsPersMngr();
		 
		 try {
			 m.selectQuery("1", 0);

		 }
		 catch (SQLException e)
		 {
				 System.out.println(e);
		 }
		 
	 
	 }
	
	
	
}
